package com.cindata

import com.cindata.dimension.BuildAreaDimension
import com.cindata.dimension.CustomDimension
import com.cindata.dimension.RiskDimension
import com.cindata.dimension.YearLimitDimension
import grails.converters.JSON
import org.hibernate.cfg.ImprovedNamingStrategy
import org.springframework.security.access.annotation.Secured

@Secured(['isAuthenticated()'])
class NewMapController
{
    def dataSource
    def springSecurityService
    def apiService

    def index()
    {
    }

    def nonPerformingAsset()
    {
        respond(view: "nonPerformingAsset")
    }

    def caseShow(){

    }

    def heatmap()
    {
        respond(view: "heatmap")
    }

    def map()
    {
        respond(view: "map")
    }

    def aroundAccount()
    {
        respond(view: "aroundAccount")
    }

    def hivemap()
    {
        respond(view: "hivemap")
    }

    def aggregation()
    {
        respond(view: "aggregation")
    }

    def website()
    {
        respond(view: "website")
    }

    /**
     * 周边网点地图条件原生SQL*/
    def aroundAccountConditionSQL(params)
    {
        def province = params["province"]
        //省
        def city = params["city"]
        //城市
        def district = params["district"]
        //行政区
        def precinct = params["precinct"]
        //片区
        def accountName = params["accountName"]
        //网点机构名称
        def accountId = params["accountId"]
        def estateSql = " "
        if (city && city != 'null' && city != '-1')
        {
            estateSql += "and a.zone_Number = '${city}' "
        }
        if (district && district != 'null' && district != '-1')
        {
            estateSql += "and b.zone_Number = '${district}' "
        }
        if (precinct && precinct != "-1")
        {
            estateSql += "and p.id = ${precinct} "
        }
        if (province && province != '-1')
        {
            estateSql += " and d.zone_Number = '${province}'"
        }
        if (accountName && accountName != '')
        {
            estateSql += " and o.name like %'${accountName}'% "
        }
        if (accountId)
        {
            estateSql += " and o.id = '${accountId}' "
        }
        return estateSql
    }

    /**
     * 网点周边地图条件*/
    def aroundAccountConditionSql(estateSql, params, conditionQueryParam, user, type)
    {
        def province = params["province"]
        //省
        def city = params["city"]
        //城市
        def district = params["district"]
        //行政区
        def precinct = params["precinct"]
        //片区
        def accountName = params["accountName"]
        //网点机构名称
        def accountId = params["accountId"]
        //网点机构Id
        estateSql = Estate.FilterAccount(estateSql, conditionQueryParam, user)
        if (city && city != '-1')
        {
            if (type == 1)
            {
                estateSql += " and e.city.zoneNumber = :city "
            }
            else if (type == 2)
            {
                estateSql += " and a.parentArea.zoneNumber = :city "
            }
            conditionQueryParam["city"] = city
        }
        if (district && district != '-1')
        {
            estateSql += " and e.district.zoneNumber = :district "
            conditionQueryParam["district"] = district
        }
        if (precinct && precinct != '-1')
        {
            estateSql += " and e.precinct.id = :precinct "
            conditionQueryParam["precinct"] = precinct
        }
        if (province && province != '-1')
        {
            estateSql += " and e.province.zoneNumber = :province "
            conditionQueryParam["province"] = province
        }
        if (accountName && accountName != '')
        {
            estateSql += " and e.account.name like :accountName "
            conditionQueryParam["accountName"] = "%" + accountName + "%"
        }
        if (accountId)
        {
            estateSql += " and e.account.id = :accountId "
            conditionQueryParam["accountId"] = Long.parseLong(accountId)
        }
        return estateSql
    }

    /**
     * 初始化地图数据
     * @return
     */
    def initMapData()
    {
        def mapDataStatus = ""
        def result = new HashMap()
        def mapDataList = new ArrayList()
        def conditionQueryParam = new HashMap()
        try
        {
            def user = User.findById(springSecurityService.principal.id)
            //登录用户
            def userAccount = user.account
            //用户所属机构
            if (!userAccount)
            {
                result["mapDataList"] = mapDataList
                render([result: result] as JSON)
                return
            }
            def currentMap = params["currentMap"]
            //地图类型
            def city = params["city"]
            def province = params["province"]
            def level = params["level"]

            if (city == "-1" && level == "1")
            {
                if (currentMap == "nonPerformingAssetMap")
                {
                    //不良资产地图
                    def sql = " select new map( a.name as name ,a.longitude as lng ,a.latitude as lat ,a.zoneNumber as zoneNumber ,count(e.id) as count ) from Estate e join e.province a where e.isNonPerformingAsset = true "
                    sql = aroundAccountConditionSql(sql, params, conditionQueryParam, user, 2)
                    sql += " group by a.name ,a.zoneNumber ,a.longitude,a.latitude "
                    mapDataList = Estate.executeQuery(sql, conditionQueryParam)
                }else{
                    //全国下的省网点
                    def sql = " select new map( a.name as name ,a.longitude as lng ,a.latitude as lat ,a.zoneNumber as zoneNumber ,count(e.id) as count ) from Estate e join e.province a where e.isEstate = true "
                    sql = aroundAccountConditionSql(sql, params, conditionQueryParam, user, 2)
                    sql += " group by a.name ,a.zoneNumber ,a.longitude,a.latitude "
                    mapDataList = Estate.executeQuery(sql, conditionQueryParam)
                }

            }
            else if (province && province != "-1" && level == "2")
            {
                //省下的市网点
                if (currentMap == "nonPerformingAssetMap")
                {
                    //不良资产地图
                    def sql = " select new map( a.name as name ,a.longitude as lng ,a.latitude as lat ,a.zoneNumber as zoneNumber ,count(e.id) as count ) from Estate e join e.city a where e.isNonPerformingAsset = true "
                    sql = aroundAccountConditionSql(sql, params, conditionQueryParam, user, 2)
                    sql += " group by a.name ,a.zoneNumber ,a.longitude,a.latitude "
                    mapDataList = Estate.executeQuery(sql, conditionQueryParam)
                }else{
                    def sql = " select new map( a.name as name ,a.longitude as lng ,a.latitude as lat ,a.zoneNumber as zoneNumber ,count(e.id) as count ) from Estate e join e.city a where e.isEstate = true "
                    sql = aroundAccountConditionSql(sql, params, conditionQueryParam, user, 2)
                    sql += " group by a.name ,a.zoneNumber ,a.longitude,a.latitude "
                    mapDataList = Estate.executeQuery(sql, conditionQueryParam)
                }


            }
            else if (city && city != '-1' && level == "3")
            {
                //市下的行政区网点
                if (currentMap == "heatMap")
                {
                    //热力图
                    def heatSql = " select new map (e.communityId as communityId , e.communityName as communityName ,avg(e.x) as lng ,avg(e.y) as lat ,count(e.id) as count ) from Estate e where e.isEstate = true "
                    heatSql = mapConditionSql(heatSql, params, conditionQueryParam, user, 1)
                    heatSql += " group by e.communityId ,e.communityName "
                    mapDataList = Estate.executeQuery(heatSql, conditionQueryParam)
                }
                else if (currentMap == "clusterMap")
                {
                    //聚合图
                    def clusterSql = " select new map (b.name as name , b.zoneNumber as zoneNumber ,b.longitude as lng ,b.latitude as lat ,count(e.id) as count ) from Estate e join e.district b where e.isEstate = true "
                    clusterSql = mapConditionSql(clusterSql, params, conditionQueryParam, user, 2)
                    clusterSql += " group by b.name ,b.zoneNumber ,b.longitude ,b.latitude "
                    mapDataList = Estate.executeQuery(clusterSql, conditionQueryParam)
                }
                else if (currentMap == "pointMap")
                {
                    //散点图
                    def pointSql = " select new map(e.x as  lng,e.y as lat,count(e.id) as count) from Estate e where e.isEstate = true "
                    pointSql = mapConditionSql(pointSql, params, conditionQueryParam, user, 1)
                    pointSql += " group by e.x ,e.y "
                    mapDataList = Estate.executeQuery(pointSql, conditionQueryParam)
                }
                else if (currentMap == "nonPerformingAssetMap")
                {
                    //不良资产地图
                    def clusterSql = " select new map (b.name as name , b.zoneNumber as zoneNumber ,b.longitude as lng ,b.latitude as lat ,count(e.id) as count ) from Estate e join e.district b where e.isNonPerformingAsset = true "
                    clusterSql = mapConditionSql(clusterSql, params, conditionQueryParam, user, 2)
                    clusterSql += " group by b.name ,b.zoneNumber ,b.longitude ,b.latitude "
                    mapDataList = Estate.executeQuery(clusterSql, conditionQueryParam)
                }
            }

            mapDataStatus = "true"
            result["mapDataStatus"] = mapDataStatus
            result["mapDataList"] = mapDataList
        }
        catch (Exception e)
        {
            mapDataStatus = "false"
            result["mapDataStatus"] = mapDataStatus
            result["mapDataList"] = mapDataList
            e.printStackTrace()
        }
        render([result: result] as JSON)
    }

    /**
     * 地图条件原生SQL*/
    def mapConditionSQL(params)
    {
        def city = params["city"]
        //城市
        def district = params["district"]
        //行政区
        def precinct = params["precinct"]
        //片区
        def startDateStart = params["startDateStart"]
        //放款时间
        def startDateEnd = params["startDateEnd"]
        //放款时间
        def loansDuration = params["loansDuration"]
        //贷款时间
        def risk = params["risk"]
        //押品风险
        def buildArea = params["buildArea"]
        //面积区域
        def communityId = params['communityId']
        //小区id
        def communityName = params['communityName']
        //小区名
        def customCode = params['customCode']
        def customValue = params['customValue']
        def leftTop = params["leftTop"]
        def rightUp = params["rightUp"]
        String[] left = new String[30]
        String[] right = new String[30]
        //地图可视范围
        def estateSql = " "
        if (city && city != 'null' && city != '-1')
        {
            estateSql += "and a.zone_Number = '${city}' "
        }
        if (district && district != 'null' && district != '-1')
        {
            estateSql += "and b.zone_Number = '${district}' "
        }
        if (precinct && precinct != "-1")
        {
            estateSql += "and p.id = ${precinct} "
        }
        if (startDateStart)
        {
            //中文日期转换
            def dates = startDateStart.toString().replaceAll("[^x00-xff]", "-")
            def dates1 = dates.toString().substring(0, dates.length() - 1) + " 00:00:00"
            estateSql += "and to_char(e.start_Date,'YYYY-MON-DD HH24:MI:SS') >=  '${dates1}' "
        }
        if (startDateEnd)
        {
            //中文日期转换
            def datee = startDateEnd.toString().replaceAll("[^x00-xff]", "-")
            def datee1 = datee.toString().substring(0, datee.length() - 1) + " 23:59:59"
            estateSql += " and to_char(e.start_Date,'YYYY-MON-DD HH24:MI:SS') <= '${datee1}' "
        }
        if (loansDuration && loansDuration != '')
        {
            def yearList = YearLimitDimension.list()
            yearList.each {
                if (it?.label == loansDuration)
                {
                    def loansDurationMin = it.minValue
                    def loansDurationMax = it.maxValue
                    estateSql += " and e.loans_Duration > ${loansDurationMin} and e.loans_Duration<= ${loansDurationMax} "
                }
            }
        }
        if (risk && risk != '')
        {
            def riskList = RiskDimension.list()
            riskList.each {
                if (it?.label == risk)
                {
                    def minValue = it.minValue / 100
                    def maxValue = it.maxValue / 100
                    estateSql += " and e.cltv >= ${minValue} and e.cltv< ${maxValue} "
                }
            }
        }
        if (buildArea && buildArea != '')
        {
            def buildAreaList = BuildAreaDimension.list()
            buildAreaList.each {
                if (it?.label == buildArea)
                {
                    def buildAreaMin = new BigDecimal(it.minValue)
                    def buildAreaMax = new BigDecimal(it.maxValue)
                    estateSql += " and e.build_Area > ${buildAreaMin} and e.build_Area <= ${buildAreaMax} "
                }
            }
        }
        if (communityId && communityId != "")
        {
            estateSql += " and e.community_Id = ${communityId} "
        }
        if (communityName && communityName != "")
        {
            estateSql += " and e.community_Name like '%${communityName}%' "
        }
        if (customValue && customCode)
        {
            def customValueList = CustomDimension.findAllByParent(CustomDimension.findAllByCode(customCode))
            ImprovedNamingStrategy dns = new ImprovedNamingStrategy()
            String column = dns.propertyToColumnName(customCode)
            customValueList.each {
                if (it?.label == customValue)
                {
                    def customMinValue = it.minValue
                    def customMaxValue = it.maxValue
                    estateSql += " and e.${column} >= ${customMinValue} and e.${column} < ${customMaxValue} "
                }
            }
        }
        if (leftTop && rightUp)
        {
            left = leftTop.split(",")
            right = rightUp.split(",")
            def maxX = right[0]
            def maxY = right[1]
            def minX = left[0]
            def minY = left[1]
            estateSql += " and e.x <= '${maxX}' and e.x >= '${minX}' and e.y <= '${maxY}' and e.y >= '${minY}' "
        }
        return estateSql
    }

    /**
     * 地图条件*/
    def mapConditionSql(estateSql, params, conditionQueryParam, user, type)
    {
        def city = params["city"]
        //城市
        def district = params["district"]
        //行政区
        def precinct = params["precinct"]
        //片区
        def startDateStart = params["startDateStart"]
        //放款时间
        def startDateEnd = params["startDateEnd"]
        //放款时间
        def loansDuration = params["loansDuration"]
        //贷款时间
        def risk = params["risk"]
        //押品风险
        def buildArea = params["buildArea"]
        //面积区域
        def communityId = params['communityId']
        //小区id
        def communityName = params['communityName']
        //小区名
        def customCode = params['customCode']
        def customValue = params['customValue']
        def leftTop = params["leftTop"]
        def rightUp = params["rightUp"]
        String[] left = new String[30]
        String[] right = new String[30]
        //地图可视范围
        estateSql = Estate.FilterAccount(estateSql, conditionQueryParam, user)
        if (city && city != 'null' && city != '-1')
        {
            if (type == 1)
            {
                estateSql += " and e.city.zoneNumber = :city "
            }
            else if (type == 2)
            {
                estateSql += " and b.parentArea.zoneNumber = :city"
            }

            conditionQueryParam['city'] = city
        }
        if (district && district != 'null' && district != '-1')
        {
            estateSql += "and e.district.zoneNumber = :district "
            conditionQueryParam['district'] = district
        }
        if (precinct && precinct != "-1")
        {
            estateSql += "and a.precinct.id = :precinct "
            conditionQueryParam['precinct'] = precinct
        }
        if (startDateStart)
        {
            //中文日期转换
            def dates = startDateStart.toString().replaceAll("[^x00-xff]", "-")
            def dates1 = dates.toString().substring(0, dates.length() - 1) + " 00:00:00"
            estateSql += "and to_char(e.startDate,'YYYY-MON-DD HH24:MI:SS') >=  :dates1 "
            conditionQueryParam['dates1'] = dates1
        }
        if (startDateEnd)
        {
            //中文日期转换
            def datee = startDateEnd.toString().replaceAll("[^x00-xff]", "-")
            def datee1 = datee.toString().substring(0, datee.length() - 1) + " 23:59:59"
            estateSql += " and to_char(e.startDate,'YYYY-MON-DD HH24:MI:SS') <= :datee1 "
            conditionQueryParam['datee1'] = datee1
        }
        if (loansDuration && loansDuration != '')
        {
            def yearList = YearLimitDimension.list()
            yearList.each {
                if (it?.label == loansDuration)
                {
                    def loansDurationMin = it.minValue
                    def loansDurationMax = it.maxValue
                    estateSql += " and e.loansDuration > :loansDurationMin and e.loansDuration<= :loansDurationMax "
                    conditionQueryParam['loansDurationMin'] = loansDurationMin
                    conditionQueryParam['loansDurationMax'] = loansDurationMax
                }
            }
        }
        if (risk && risk != '')
        {
            def riskList = RiskDimension.list()
            riskList.each {
                if (it?.label == risk)
                {
                    def minValue = it.minValue / 100
                    def maxValue = it.maxValue / 100
                    estateSql += " and e.cltv >= :minValue and e.cltv< :maxValue "
                    conditionQueryParam['minValue'] = minValue
                    conditionQueryParam['maxValue'] = maxValue
                }
            }
        }
        if (buildArea && buildArea != '')
        {
            def buildAreaList = BuildAreaDimension.list()
            buildAreaList.each {
                if (it?.label == buildArea)
                {
                    def buildAreaMin = new BigDecimal(it.minValue)
                    def buildAreaMax = new BigDecimal(it.maxValue)
                    estateSql += " and e.buildArea > :buildAreaMin and e.buildArea <= :buildAreaMax "
                    conditionQueryParam['buildAreaMin'] = buildAreaMin
                    conditionQueryParam['buildAreaMax'] = buildAreaMax
                }
            }
        }
        if (communityId && communityId != "")
        {
            estateSql += " and e.communityId = :communityId "
            conditionQueryParam['communityId'] = Long.parseLong(communityId)
        }
        if (communityName && communityName != "")
        {
            estateSql += " and e.communityName like :communityName "
            conditionQueryParam['communityName'] = "%" + communityName + "%"
        }
        if (customValue && customCode)
        {
            def customValueList = CustomDimension.findAllByParent(CustomDimension.findAllByCode(customCode))
            customValueList.each {
                if (it?.label == customValue)
                {
                    def customMinValue = it.minValue
                    def customMaxValue = it.maxValue
                    estateSql += " and e.${customCode} >= ${customMinValue} and e.${customCode} < ${customMaxValue} "
                }
            }
        }
        if (leftTop && rightUp)
        {
            left = leftTop.split(",")
            right = rightUp.split(",")
            def maxX = right[0]
            def maxY = right[1]
            def minX = left[0]
            def minY = left[1]
            estateSql += " and e.x <= :maxX and e.x >= :minX and e.y <= :maxY and e.y >= :minY "
            conditionQueryParam['maxX'] = Double.parseDouble(maxX)
            conditionQueryParam['minX'] = Double.parseDouble(minX)
            conditionQueryParam['maxY'] = Double.parseDouble(maxY)
            conditionQueryParam['minY'] = Double.parseDouble(minY)
        }
        return estateSql
    }

    def point()
    {
        respond(view: "point")
    }

    def cluster()
    {
        respond(view: "cluster")
    }

    def gradRender()
    {
        respond(view: "gradRender")
    }

    def gisMap()
    {
        respond(view: "gisMap")
    }

}

